<?php
namespace app\admin\controller\v1;
use app\admin\Adminbase;
use app\admin\controller\soft\Param;
use base\date\Dates;

class Finance extends Adminbase
{
	
	/**
	* 今日营业额
	* /admin/v1/finance/turnover
	* 需要登录 
	* @is_need_login true
	* @method post
	*/
	public function turnover($shop_id,$year,$month=''){
		try {
			
			$data['y'] = [];
			$data['x'] = [];
			if( !$month ){
				for ($i=1; $i<=12; $i++){
					$model = model('order');
					$model = $model->where("shop_id",$shop_id ?: 0);
					$model = $model->where("is_pay",1);
					$model = $model->where('status','>=',0);
					$month = date('Y-m',strtotime(date($year.'-'.$i)));
					$data['y'][] = $model->where('add_time','like',"%$month%")->sum('pay_price');
					$data['x'][] = $i.'月';
			    }
			}else{
				$dates = Dates::getMonthDays( date($year.'-'.$month.'-01') );
				foreach($dates as $key=>$value){
					$model = model('order');
					$model = $model->where("shop_id",$shop_id ?: 0);
					$model = $model->where("is_pay",1);
					$model = $model->where('status','>=',0);
					$month = $value['date'];
					$data['y'][] = $model->where('add_time','like',"%$month%")->sum('pay_price');
					$data['x'][] = $value['day'];
				}
			}
			
			return $this->ReSucess($data);
			
		} catch (\Exception $e) {
			return $this->ReError($e->getMessage());	
		}
	}

    /**
     * 店面业绩分析
     * /admin/v1/finance/analysis
     * 需要登录
     * @is_need_login true
     * @method post
     */
    public function analysis(){
        //$request =  request();  获取Tp5的所有请求

       $keyword_array = json_decode($this->request->param('keyWord'),true);



        $shop_id= $keyword_array['filter'][0]['value'];
        $year= $keyword_array['filter'][1]['value'];
        $jd=$keyword_array['filter'][2]['value'];
        $cate_id=$keyword_array['filter'][3]['value'];

        $str="";
        $str_last="";

           $str.=" and shop_id=".$shop_id;
           $str_last.=" and shop_id=".$shop_id;

        if($year){
            $str.=" and date_format(add_time,'%Y-%m-%d') like '%$year%'";
        }
        if($jd){
            $str.=" and quarter(add_time) =".$jd;
            $str_last.=" and quarter(add_time) =".$jd;
        }
        if($cate_id){
            $str.=" and cate_id=".$cate_id;
            $str_last.=" and cate_id=".$cate_id;
        }
        $str_limit=" order by cate_id asc "."limit ".($this->request->post('pageIndex')-1)*( $this->request->post('pageSize')).",".$this->request->post('pageSize');

        $year_date=$year."-01-01";

        $sql=" select * from (
					select
						a.*,
					ifnull(b.yj,0.00) as yj_1,
					ifnull(concat(CONVERT((b.yj/bt.bs )*100,DECIMAL (10,2)),'%'),0.00) as zb_1,
					0.00 as tbzz_1,
					ifnull(c.yj,0.00) as yj_2,
					ifnull(concat(CONVERT((c.yj/ct.cs )*100,DECIMAL (10,2)),'%'),0.00) as zb_2,
					0.00 as tbzz_2,
					ifnull(d.yj,0.00) as yj_3,
					ifnull(concat(CONVERT((d.yj/dt.ds )*100,DECIMAL (10,2)),'%'),0.00) as zb_3,
					0.00 as tbzz_3
					from (
						SELECT
							aa.add_time,quarter(aa.add_time) as jd
							,concat(ee.name,'-',dd.name) as classify
							,cc.cate_id,aa.shop_id
						from xw_order aa
						LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
						LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id
						LEFT JOIN xw_goods_cate as dd on cc.cate_id  = dd.id
						LEFT JOIN xw_goods_cate as ee on dd.pid  = ee.id
						where aa.is_del=0 and aa.status>=0 and aa.is_pay=1 and  year(aa.add_time)=year('$year_date')
						GROUP BY quarter(aa.add_time),cc.cate_id
					) a
					LEFT JOIN (
							SELECT aa.id,aa.add_time,cc.cate_id,
								SUM(aa.pay_price) as yj,
								
								case
									when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
									when ROUND(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
									when ROUND(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
								end as 'month'
							FROM xw_order aa
							LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
							LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id  where year(aa.add_time)=year('$year_date')
							GROUP BY month(aa.add_time),month,cc.cate_id
					) b on b.month = 1 and quarter(b.add_time) = quarter(a.add_time) and b.cate_id = a.cate_id
					LEFT JOIN (
							SELECT aa.id,aa.add_time,cc.cate_id,
								SUM(aa.pay_price) as yj,
							
								case
									when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
									when ROUND(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
									when ROUND(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
								end as 'month'
							FROM xw_order aa
							LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
							LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id  where year(aa.add_time)=year('$year_date')
							GROUP BY month(aa.add_time),month,cc.cate_id
					) c on c.month = 2 and quarter(c.add_time) = quarter(a.add_time) and c.cate_id = a.cate_id
					LEFT JOIN (
							SELECT aa.id,aa.add_time,cc.cate_id,
								SUM(aa.pay_price) as yj,
							
								case
									when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
									when ROUND(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
									when ROUND(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
								end as 'month'
							FROM xw_order aa
							LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
							LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id where year(aa.add_time)=year('$year_date')
							GROUP BY month(aa.add_time),month,cc.cate_id
					) d on d.month = 3 and quarter(d.add_time) = quarter(a.add_time) and d.cate_id = a.cate_id

				LEFT JOIN (
						   select sum(b_s.yj) bs from (
										select  sum(aa.pay_price) as yj,
										 case when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
											  when round(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
											  when round(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
										end as 'month'
										from xw_order aa left join xw_order_goods as bb on aa.id = bb.order_id
										left join xw_goods as cc on bb.goods_id = cc.id where year(aa.add_time)=year('$year_date')
										group by month(aa.add_time),month,cc.cate_id
						) b_s

					) bt on b.month = 1 and quarter(b.add_time) = quarter(a.add_time) and b.cate_id = a.cate_id

					LEFT JOIN (
						   select sum(c_s.yj) cs from (
							select sum(aa.pay_price) as yj,
									case
										when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
										when round(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
										when round(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
									end as 'month'
								from xw_order aa
								left join xw_order_goods as bb on aa.id = bb.order_id
								left join xw_goods as cc on bb.goods_id = cc.id  where year(aa.add_time)=year('$year_date')
								group by month(aa.add_time),month,cc.cate_id
						) c_s
					) ct on c.month = 2 and quarter(c.add_time) = quarter(a.add_time) and c.cate_id = a.cate_id

					LEFT JOIN (
						   select sum(d_s.yj) ds from (
							select aa.id,aa.add_time,cc.cate_id, sum(aa.pay_price) as yj,
									case
										when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
										when round(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
										when round(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
									end as 'month'
								from xw_order aa
								left join xw_order_goods as bb on aa.id = bb.order_id
								left join xw_goods as cc on bb.goods_id = cc.id  where year(aa.add_time)=year('$year_date')
								group by month(aa.add_time),month,cc.cate_id
						) d_s
					) dt on d.month = 3 and quarter(d.add_time) = quarter(a.add_time) and d.cate_id = a.cate_id


				) a where (1=1 $str) ".$str_limit;

        $tables = \Db::query($sql);

        //获取去年的数
        $last_data=$this->last_analysis($year."-01-01",$str_last)['data'];

         foreach($tables as $k=>$v){

             foreach ($last_data as $key=>$value){
                 if($value['cate_id']==$v['cate_id'] && $value['jd']==$v['jd']){

                     $tables[$k]['tbzz_1']=$value['yj_1']!='0.00'?sprintf("%.4f",floatval(($v['yj_1']-$value['yj_1'])/$value['yj_1']*0.01)).'%':'0.00';
                     $tables[$k]['tbzz_2']=$value['yj_2']!='0.00'?sprintf("%.4f",floatval(($v['yj_2']-$value['yj_2'])/$value['yj_2']*0.01)).'%':'0.00';
                     $tables[$k]['tbzz_3']=$value['yj_3']!='0.00'?sprintf("%.4f",floatval(($v['yj_3']-$value['yj_3'])/$value['yj_3']*0.01)).'%':'0.00';

                 }

             }

         }

        $data["total"]= count($tables);
        $data["data"]= $tables;
        return $data;
    }

    //获得上年的每一个季度下的月总业绩
    public function last_analysis($year,$str_last)
    {


        $sql=" select * from (
					select
						a.*,
				  	ifnull(b.yj,0.00) as yj_1,
						ifnull(c.yj,0.00) as yj_2,
            ifnull(d.yj,0.00) as yj_3
			
				
					from (

						SELECT
							aa.add_time,quarter(aa.add_time) as jd
							,concat(ee.name,'-',dd.name) as classify
							,cc.cate_id,aa.shop_id,aa.pay_price
						from xw_order aa
						LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
						LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id
						LEFT JOIN xw_goods_cate as dd on cc.cate_id  = dd.id
						LEFT JOIN xw_goods_cate as ee on dd.pid  = ee.id
						where aa.is_del=0 and aa.status>=0 and aa.is_pay=1 and year(aa.add_time)=year(date_sub('$year',interval 1 year))
						GROUP BY quarter(aa.add_time),cc.cate_id
					) a
					LEFT JOIN (
							SELECT aa.id,aa.add_time,cc.cate_id,
								SUM(aa.pay_price) as yj,
								
								case
									when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
									when ROUND(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
									when ROUND(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
								end as 'month'
							FROM xw_order aa
							LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
							LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id where  year(aa.add_time)=year(date_sub('$year',interval 1 year))
							GROUP BY month(aa.add_time),month,cc.cate_id 
					) b on b.month = 1 and quarter(b.add_time) = quarter(a.add_time) and b.cate_id = a.cate_id  
					LEFT JOIN (
							SELECT aa.id,aa.add_time,cc.cate_id,
								SUM(aa.pay_price) as yj,
								
								case
									when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
									when ROUND(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
									when ROUND(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
								end as 'month'
							FROM xw_order aa
							LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
							LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id where  year(aa.add_time)=year(date_sub('$year',interval 1 year))
							GROUP BY month(aa.add_time),month,cc.cate_id 
					) c on c.month = 2 and quarter(c.add_time) = quarter(a.add_time) and c.cate_id = a.cate_id  
					LEFT JOIN (
							SELECT aa.id,aa.add_time,cc.cate_id,
								SUM(aa.pay_price) as yj,
								
								case
									when month(aa.add_time) / 3 = quarter(aa.add_time) then 3
									when ROUND(month(aa.add_time) / 3) = quarter(aa.add_time) then 2
									when ROUND(month(aa.add_time) / 3) + 1 = quarter(aa.add_time) then 1
								end as 'month'
							FROM xw_order aa
							LEFT JOIN xw_order_goods as bb on aa.id  = bb.order_id
							LEFT JOIN xw_goods as cc on bb.goods_id  = cc.id where  year(aa.add_time)=year(date_sub('$year',interval 1 year))
							GROUP BY month(aa.add_time),month,cc.cate_id 
					) d on d.month = 3 and quarter(d.add_time) = quarter(a.add_time) and d.cate_id = a.cate_id 
					

				) a where (1=1 $str_last) ";

        $tables = \Db::query($sql);
        $data["total"]= count($tables);
        $data["data"]= $tables;
        return $data;

    }

}



